package cn.com.lms.business.learning.persistence.repository;

import cn.com.lms.business.learning.persistence.entity.FbLearningStudent;
import com.bnzj.core.persistence.repository.BaseRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public interface LearningStudentRepository extends BaseRepository<FbLearningStudent, Long> {

    @Modifying
    @Query(value = "delete from bnz_learning_student  where plan_id=?1", nativeQuery = true)
    int delByPlanId(Long planId);

    @Query(value = "select count(id) num from bnz_learning_student where stage_detail_id = ?1 and finish_state = ?2 ", nativeQuery = true)
    int getCountByStageDetailId(Long id, Integer state);


    @Modifying
    @Query(value = "update  bnz_learning_student  set last_study_time = ?1,learning_hours = ?2 where id = ?3 and user_id = ?4", nativeQuery = true)
    void updateLearningHours(Date date, Integer hours, Long id, Long userId);

    /*
     * @author liuwei
     * @date 2020/4/1 10:39
     * @desc //查看学习计划下所有学员信息
     */
    @Query(value = "select  user_id as userId,user_name as userName from bnz_learning_student where plan_id=?1 and dept_name is  null  GROUP BY user_id,user_name", nativeQuery = true)
    List<Map<String, Object>> queryByLearningId(Long learningId);

    /*
     * @author liuwei
     * @date 2020/4/1 10:39
     * @desc //查看学习计划阶段下部门信息
     */
    @Query(value = "select dept_id deptId,dept_name deptName from bnz_learning_student where plan_id=?1 and dept_name is not null  GROUP BY dept_name", nativeQuery = true)
    List<Map<String, Object>> queryDeptInfoByLearningId(Long learningId);

    /*
     * @author liuwei
     * @date 2020/3/24 10:32
     * @desc 查看用户学习计划详情sql
     */
    @Query(value = "SELECT lp.id,lp.plan_name planName,lp.cover_url coverUrl,lp.creator_name createName,lp.finish_time finishTime,lp.create_time createTime,count(DISTINCT ld.stage_id) AS stageCount," +
            " count(DISTINCT ld.id) stageDetailCount,(select sum(course_time) from bnz_course_info where  FIND_IN_SET(id,GROUP_CONCAT( ld.obj_id ))) " +
            " as sumHours,sum(lsd.learning_hours) hours,(select count(id) from bnz_learning_student fls where fls" +
            ".plan_id=lp.id and user_id=?1 and fls.finish_state = 1) finishNum" +
            " FROM bnz_learning_student lsd INNER JOIN bnz_learning_stage_detail ld on lsd.stage_detail_id = ld.id " +
            " inner join bnz_learning_plan lp on lp.id = lsd.plan_id where lsd.user_id = ?1 and lp.id = ?2 ", nativeQuery = true)
    Map<String, Object> getUserLearningPlan(Long userId, Long learningId);


    // 查询学生阶段课程的已学习信息
    @Query(value = "select * from bnz_learning_student where stage_detail_id=?1 AND user_id=?2", nativeQuery = true)
    FbLearningStudent userStageCourseLearningInfo(long stageDetailId, long userId);

    // 更新学生学习记录信息
    @Modifying
    @Query(value = "UPDATE bnz_learning_student SET learning_hours=learning_hours+?3,finish_state=?4,last_study_time=?5 WHERE stage_detail_id=?1 AND user_id=?2", nativeQuery = true)
    int updateUserLearningInfo(long stageDetailId, long userId, int duration, int finishState, Date outSignTime);

    /**
     * @param id
     * @param planId
     * @return 根据用户ID和阶段详情ID查询
     */
    FbLearningStudent findByUserIdAndStageDetailId(Long id, Long planId);
}
